/*============================================================================
  File:     RestoreSequence.sql

  Summary:  This script shows how to examine the 
			restore sequence of your backups.

------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you give due credit.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE master;
GO

-- first thing - tail-of-the-log backup
BACKUP LOG <database_name>
TO DISK = N'D:\MSSQL\Backup\<database_name>_Log_Tail.bak'
WITH INIT, NO_TRUNCATE;
GO

/*
Now we have to restore. What backups do we have?

We need the most recent full backup, the most recent
differential backup, and then all the log backups after that,
and finally the tail-of-the-log backup
*/
-- backup history tables
SELECT
	s.backup_start_date,
	s.database_name,
	(CASE s.[type]
		WHEN N'D' THEN N'Full'
		WHEN N'I' THEN N'Diff'
		WHEN N'L' THEN N'Log'
		ELSE N'Unknown'
	END) AS 'backup_type',
	s.backup_set_id,
	s.position,
	m.family_sequence_number,
	s.[name],
	s.[description],
	s.first_lsn, 
	s.last_lsn, 
	s.backup_finish_date,
	m.physical_device_name
FROM msdb.dbo.backupset AS s
INNER JOIN msdb.dbo.backupmediafamily AS m
ON s.media_set_id = m.media_set_id
-- WHERE s.database_name = N'<database_name>'
ORDER BY s.backup_start_date ASC;
GO

-- now do the restore
USE master;
GO

-- start with the most recent full backup
RESTORE DATABASE <database_name>
FROM DISK = N'D:\MSSQL\Backup\<database_name>_Full.bak'
WITH NORECOVERY, REPLACE;

-- then the most recent differential backup
RESTORE DATABASE <database_name>
FROM DISK =	N'D:\MSSQL\Backup\<database_name>_Diff.bak'
WITH FILE = <X>, NORECOVERY;

-- and finally all the log backups after that
RESTORE DATABASE <database_name>
FROM DISK =	N'D:\MSSQL\Backup\<database_name>_Log.bak'
WITH FILE = <Y>, NORECOVERY;
GO

RESTORE DATABASE <database_name>
FROM DISK =	N'D:\MSSQL\Backup\<database_name>_Log.bak'
WITH FILE = <Z>, NORECOVERY;
GO

-- including the tail of the log backup
RESTORE DATABASE <database_name>
FROM DISK = N'D:\MSSQL\Backup\<database_name>_Log_Tail.bak'
WITH NORECOVERY;
GO

-- and bring the database online
RESTORE DATABASE <database_name> WITH RECOVERY;
GO

USE <database_name>;
GO

SELECT
	database_id,
	name AS database_name,
	user_access_desc,
	is_read_only,
	state_desc
FROM sys.databases
WHERE name = N'<database_name>';